/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import bean.Instituicao;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author fernando
 */
public class InstituicaoDao implements Serializable {

    private Conexao conexao;
    private PreparedStatement ps;
    private boolean sucess = false;

    public InstituicaoDao() {
        conexao = new ConexaoPostgres();
    }

    public boolean cadastrarInstituicao(Instituicao instituicao) {
        sucess = false;

        try {
            System.out.println("cadastro instituicao: " + instituicao.getNome());
            ps = conexao.getConnection().prepareStatement("INSERT INTO INSTITUICAO(nome, estado, municipio) VALUES (?, ?, ?) ");
            ps.setString(1, instituicao.getNome());
            ps.setString(2, instituicao.getEstado());
            ps.setString(3, instituicao.getMunicipio());
            ps.executeUpdate();
            conexao.closeConnection();

            sucess = true;
        } catch (SQLException ex) {
            conexao.closeConnection();
            Logger.getLogger(InstituicaoDao.class.getName()).log(Level.SEVERE, null, ex);
            return sucess;
        }

        conexao.closeConnection();
        return sucess;
    }

    public boolean alterarInstituicao(Instituicao instituicao) {
        sucess = false;


        try {
            
            ps = conexao.getConnection().prepareStatement("UPDATE instituicao SET nome = ?, estado = ?, municipio = ? where id = ?");

            ps.setString(1, instituicao.getNome());
            ps.setString(2, instituicao.getEstado());
            ps.setString(3, instituicao.getMunicipio());
            ps.setInt(4, instituicao.getId());
            ps.executeUpdate();
            conexao.closeConnection();
            
            sucess = true;
                    
        } catch (SQLException ex) {
            conexao.closeConnection();
            Logger.getLogger(InstituicaoDao.class.getName()).log(Level.SEVERE, null, ex);
            return sucess;
        }

        conexao.closeConnection();
        return sucess;
    }

    public boolean removerInstituicao(Instituicao instituicao) {
        sucess = false;
        try {

            ps = conexao.getConnection().prepareStatement(" DELETE FROM instituicao WHERE id = ?");

            ps.setLong(1, (instituicao.getId()));

            ps.executeUpdate();
            conexao.closeConnection();


            sucess = true;
        } catch (SQLException ex) {
            conexao.closeConnection();
            ex.printStackTrace();
            return sucess;
        }

        conexao.closeConnection();
        return sucess;
    }

    public List<Instituicao> listarInstituicoes() {
        List<Instituicao> instituicoes = new ArrayList<Instituicao>();
        try {
            ps = conexao.getConnection().prepareStatement("SELECT * FROM instituicao ORDER BY nome");
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();

            while (rs.next()) {
                Instituicao i = new Instituicao();

                i.setEstado(rs.getString("estado"));
                i.setMunicipio(rs.getString("municipio"));
                i.setNome(rs.getString("nome"));
                i.setId(rs.getInt("id"));

                instituicoes.add(i);


            }
        } catch (SQLException ex) {
            conexao.closeConnection();
            Logger.getLogger(InstituicaoDao.class.getName()).log(Level.SEVERE, null, ex);
        }



        conexao.closeConnection();
        return instituicoes;

    }

    public Instituicao consultaInstituicao(int id) {
        PreparedStatement ps = null;
        Instituicao c = null;
        try {
            c = new Instituicao();
            ps = conexao.getConnection().prepareStatement("Select * from instituicao where id = ?; ");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();
            if (rs.next()) {

                c.setNome(rs.getString("nome"));
                c.setMunicipio(rs.getString("municipio"));
                c.setEstado(rs.getString("estado"));
                c.setId(id);


            }
        } catch (SQLException ex) {
            conexao.closeConnection();
            return c;

        }
        conexao.closeConnection();
        return c;
    }
}
